﻿Imports Kimmo

Public Class clsBusinessEntity
    Private Shared BE As StringBuilder
    Private Shared Property TableID As Integer = 0
    Private Shared Property TableName As String = ""
    Private Shared Property TargetFolder As String = ""
    Private Shared Property ClassName As String = ""
    Private Shared Property TargetFile As String = ""
    Private Shared Property DateFields As List(Of String)
    Private Shared Property FieldsToUpdate As List(Of String)
    Private Shared ToolTipField As String = ""
    Private Shared PrimaryKey As String = ""
    Private Shared Property Fields As List(Of FieldDataType)

    Public Class PropertyBuilder
        Private Shared Property Delim As String = ", "
        Private Shared Property PB As StringBuilder

        Public Shared Sub Reset()
            PB = New StringBuilder
        End Sub

        Public Shared Sub AddProperty(ByVal propName As String)
            Dim sLine As String = clsDL.QuoteString(propName)
            PB.Append(sLine & Delim)
        End Sub

        Public Shared Sub AddProperty(ByVal propName As String, ByVal bQuote As Boolean)
            PB.Append(propName & Delim)
        End Sub


        Public Shared Function Build()
            Return clsDL.RemDelim(PB.ToString, ", ")
        End Function
    End Class


    Public Class FieldDataType
        Public Property FieldName As String = ""
        Public Property FieldType As String = ""
        Public Property FieldDefault As String = ""
        Public Property FieldPrefix As String = ""
        Public Property FieldVariable As String = ""
        Public Property FieldFilter As String = ""
        Public Property FilterStart As String = ""
        Public Property FilterEnd As String = ""
        Public Property Filter As String = ""
        Public Property ReadValue As String = ""
        Public Property ByReturnValue As String = ""

        Public Sub New(FldName As String, FldType As String, FldDefault As String, FldPrefix As String)
            MyBase.New()
            Me.FieldName = FldName
            Me.FieldType = FldType
            Me.FieldDefault = FldDefault
            Me.FieldPrefix = FldPrefix
            Me.FieldVariable = FldPrefix & FldName
            Select Case FldPrefix
                Case "int", "lng", "sng", "dbl"
                    Me.FieldFilter = Me.FieldVariable
                    Me.FilterStart = ""
                    Me.FilterEnd = ""
                    Me.ReadValue = "Val(xRow(" & clsDL.QuoteString(Me.FieldName) & ").ToString)"
                    Me.ByReturnValue = "Val(mv)"
                Case "dat"
                    Me.FieldFilter = Me.FieldVariable
                    Me.FilterStart = ""
                    Me.FilterEnd = ""
                    Me.ReadValue = "IIf((IsDate(xRow(" & clsDL.QuoteString(Me.FieldName) & ").ToString) = True), CDate(xRow(" & clsDL.QuoteString(Me.FieldName) & ").ToString), Nothing)"
                    Me.ByReturnValue = "IIf((IsDate(mv) = True), CDate(mv), Nothing)"
                Case "bool"
                    Me.FieldFilter = Me.FieldVariable
                    Me.FilterStart = ""
                    Me.FilterEnd = ""
                    Me.ReadValue = "Conversions.ToBoolean(xRow(" & clsDL.QuoteString(Me.FieldName) & ")"
                    Me.ByReturnValue = "IIf((Len(mv) > 0), Conversions.ToBoolean(mv), False)"
                Case "img"
                    Me.FieldFilter = Me.FieldVariable
                    Me.FilterStart = ""
                    Me.FilterEnd = ""
                    Me.ReadValue = "xRow(" & clsDL.QuoteString(Me.FieldName) & ")"
                    Me.ByReturnValue = "mv"
                Case "str"
                    Me.FilterStart = "'"
                    Me.FieldFilter = "'" & Me.FieldVariable & "'"
                    Me.FilterEnd = " & " & clsDL.Quote & "'" & clsDL.Quote
                    Me.ReadValue = "xRow(" & clsDL.QuoteString(Me.FieldName) & ").ToString"
                    Me.ByReturnValue = "mv"
            End Select
            Me.Filter = Me.FilterStart & clsDL.Quote & " & " & Me.FieldVariable & Me.FilterEnd
        End Sub
    End Class

    Private Shared Function VbTypeFromDataType(ByVal sDataType As String) As String
        Select Case sDataType.ToLower
            Case "integer", "long", "single", "double"
                Return sDataType
            Case "date"
                Return "Nullable (of Date)"
            Case "text", "longtext"
                Return "String"
            Case "yesno"
                Return "Boolean"
            Case "image"
                Return "Object"
            Case Else
                Return "String"
        End Select
    End Function

    Private Shared Function VbDataTypePrefix(ByVal sDataType As String) As String
        Select Case sDataType.ToLower
            Case "integer" : Return "int"
            Case "long" : Return "lng"
            Case "single" : Return "sng"
            Case "double" : Return "dbl"
            Case "date" : Return "dat"
            Case "text", "longtext"
                Return "str"
            Case "yesno" : Return "bool"
            Case "image" : Return "img"
            Case Else : Return "str"
        End Select
    End Function

    Private Shared Function VbDefaultFromDataType(ByVal sDataType As String, ByVal sDefault As String) As String
        Select Case sDataType.ToLower
            Case "integer", "long", "single", "double"
                If Len(sDefault) = 0 Then
                    Return "0"
                Else
                    Return "Val(" & sDefault & ")"
                End If
            Case "date"
                If Len(sDefault) = 0 Then
                    Return "Nothing"
                Else
                    Return "CDate(" & sDefault & ")"
                End If
            Case "text", "longtext"
                Return clsDL.QuoteString("")
            Case "yesno"
                If Len(sDefault) = 0 Then
                    Return False
                Else
                    Return sDefault
                End If
            Case "image"
                Return "Nothing"
            Case Else
                Return clsDL.QuoteString("")
        End Select
    End Function

    Public Shared Function Build(ByVal iTableID As Integer) As String
        TableID = iTableID
        If TableID <= 0 Then
            Throw New Exception("Please specify the table id of the table to process first!")
            Exit Function
        End If
        ' get the path of the project
        TargetFolder = clsTables.ProjectPathByID(TableID.ToString)
        TableName = clsTables.TableNameByID(TableID.ToString)
        ClassName = TableName.Replace("_", "").Replace(" ", "")
        TargetFile = TargetFolder & "/cls" & ClassName & ".vb"
        ' start processing
        BE = New StringBuilder
        BE.AppendLine("Imports DocumentFormat.OpenXml.Spreadsheet")
        BE.AppendLine("Imports FineUI")
        BE.AppendLine("Imports Kimmo")
        BE.AppendLine("Imports Microsoft.VisualBasic.CompilerServices")
        BE.AppendLine("Imports System.Collections")
        BE.AppendLine("Imports System.Collections.Generic")
        BE.AppendLine("Imports System.ComponentModel")
        BE.AppendLine("Imports System.Data")
        BE.AppendLine("Imports System.Data.SqlServerCe")
        BE.AppendLine("Imports System.Diagnostics")
        BE.AppendLine("Imports System.Runtime.CompilerServices")
        BE.AppendLine("")
        AddComments("This is the beginning of the " & TableName & " business object to handle interaction between the user interface and the database.")
        BE.AppendLine("Public Class cls" & ClassName)
        ToolTipField = clsColumns.ToolTipField(TableID)
        PrimaryKey = clsColumns.PrimaryKey(TableID)
        Build_Properties()
        Build_ClearNew()
        Build_Delete()
        Build_FieldBy()
        Build_Lists()
        Build_Count()
        Build_Exists()
        Build_Read()
        Build_Save()
        Build_Grid()
        Build_ShowGrid()
        Build_Import()
        Build_Blank()
        'Build_ListsBy()
        BE.AppendLine("End Class")
        ' ensure file is deleted first
        WebSite.File_Delete(TargetFile)
        WebSite.File_Update(TargetFile, BE.ToString)
        Return TargetFile
    End Function

    Public Shared Sub AddComments(ByVal sPurpose As String)
        BE.AppendLine("''' <summary>")
        BE.AppendLine("''' " & sPurpose)
        BE.AppendLine("''' </summary>")
        BE.AppendLine("''' <remarks>This code is automatically generated by SystemBuilder.Show.</remarks>")
    End Sub

    Private Shared Sub Build_Grid()
        BE.AppendLine("")
        AddComments("This method builds up an editable grid based on selected records. The grid has Save, Clone and Delete buttons")
        BE.AppendLine("Public Shared Sub Show" & TableName & "(ByRef grdViews As FineUI.Grid, ByVal strFilter As String)")
        ' init values
        BE.AppendLine("clsFineUI.Grid.Clear(grdViews, " & clsDL.QuoteString("") & ", " & clsDL.QuoteString("") & ")")
        BE.AppendLine("clsFineUI.Grid.TableName = " & clsDL.QuoteString(TableName))
        BE.AppendLine("clsFineUI.Grid.DataKeyNames = " & clsDL.QuoteString(PrimaryKey))
        BE.AppendLine("clsFineUI.Grid.PageSize = 50")
        BE.AppendLine("clsFineUI.Grid.AllowPaging = True")
        BE.AppendLine("clsFineUI.Grid.AllowSorting = True")
        BE.AppendLine("clsFineUI.Grid.LoadMask = False")
        BE.AppendLine("clsFineUI.Grid.Title = " & clsDL.QuoteString(TableName))
        BE.AppendLine("clsFineUI.Grid.ShowBorder = False")
        BE.AppendLine("clsFineUI.Grid.ShowHeader = False")
        BE.AppendLine("clsFineUI.Grid.EnableRowNumber = False")
        BE.AppendLine("clsFineUI.Grid.AutoHeight = True")
        BE.AppendLine("clsFineUI.Grid.AutoPostBack = False")
        BE.AppendLine("clsFineUI.Grid.EnableCheckBoxSelect = True")
        BE.AppendLine("clsFineUI.Grid.AutoWidth = True")
        BE.AppendLine("clsFineUI.Grid.EnableAlternateRowColor = True")
        BE.AppendLine("clsFineUI.Grid.EnableCollapse = False")
        BE.AppendLine("clsFineUI.Grid.EnableMouseOverColor = True")
        BE.AppendLine("clsFineUI.Grid.EnableMultiSelect = True")
        BE.AppendLine("clsFineUI.Grid.EnableViewState = True")
        BE.AppendLine("clsFineUI.Grid.EnableAjax = True")
        BE.AppendLine("clsFineUI.Grid.ColumnLines = True")
        BE.AppendLine("clsFineUI.Grid.ToolTipField = " & clsDL.QuoteString(ToolTipField))
        BE.AppendLine("clsFineUI.Grid.IsDatabasePaging = False")
        BE.AppendLine("clsFineUI.Grid.ShowGridHeader = True")
        BE.AppendLine("clsFineUI.Grid.EnableHeaderMenu = True")
        BE.AppendLine("clsFineUI.Grid.EnableColumnHide = True")
        Build_GridColumns()
        BE.AppendLine("clsFineUI.Grid.AddSave()")
        BE.AppendLine("clsFineUI.Grid.AddClone()")
        BE.AppendLine("clsFineUI.Grid.AddDelete()")
        BE.AppendLine("If Len(strFilter) > 0 Then clsFineUI.Grid.Filter = strFilter")
        BE.AppendLine("clsFineUI.Grid.OrderBy = " & clsDL.QuoteString(PrimaryKey))
        BE.AppendLine("clsFineUI.Grid.BuildSelectCommand()")
        BE.AppendLine("clsFineUI.Grid.UseSQLCompact = True")
        BE.AppendLine("clsFineUI.Grid.Update(Nothing)")
        BE.AppendLine("End Sub")
    End Sub

    Private Shared Sub Build_ShowGrid()
        BE.AppendLine("")
        AddComments("Builds a readonly grid based on selected records, this grid has Edit, Clone, Delete, Like and Comment buttons.")
        BE.AppendLine("Public Shared Sub ShowGrid(ByRef grdViews As FineUI.Grid, ByVal strFilter As String)")
        ' init values
        BE.AppendLine("clsFineUI.Grid.Clear(grdViews, " & clsDL.QuoteString("") & ", " & clsDL.QuoteString("") & ")")
        BE.AppendLine("clsFineUI.Grid.TableName = " & clsDL.QuoteString(TableName))
        BE.AppendLine("clsFineUI.Grid.DataKeyNames = " & clsDL.QuoteString(PrimaryKey))
        BE.AppendLine("clsFineUI.Grid.PageSize = 50")
        BE.AppendLine("clsFineUI.Grid.AllowPaging = True")
        BE.AppendLine("clsFineUI.Grid.AllowSorting = True")
        BE.AppendLine("clsFineUI.Grid.LoadMask = False")
        BE.AppendLine("clsFineUI.Grid.Title = " & clsDL.QuoteString(TableName))
        BE.AppendLine("clsFineUI.Grid.ShowBorder = False")
        BE.AppendLine("clsFineUI.Grid.ShowHeader = False")
        BE.AppendLine("clsFineUI.Grid.EnableRowNumber = False")
        BE.AppendLine("clsFineUI.Grid.AutoHeight = True")
        BE.AppendLine("clsFineUI.Grid.AutoPostBack = False")
        BE.AppendLine("clsFineUI.Grid.EnableCheckBoxSelect = True")
        BE.AppendLine("clsFineUI.Grid.AutoWidth = True")
        BE.AppendLine("clsFineUI.Grid.EnableAlternateRowColor = True")
        BE.AppendLine("clsFineUI.Grid.EnableCollapse = False")
        BE.AppendLine("clsFineUI.Grid.EnableMouseOverColor = True")
        BE.AppendLine("clsFineUI.Grid.EnableMultiSelect = True")
        BE.AppendLine("clsFineUI.Grid.EnableViewState = True")
        BE.AppendLine("clsFineUI.Grid.EnableAjax = True")
        BE.AppendLine("clsFineUI.Grid.ColumnLines = True")
        BE.AppendLine("clsFineUI.Grid.ToolTipField = " & clsDL.QuoteString(ToolTipField))
        BE.AppendLine("clsFineUI.Grid.IsDatabasePaging = False")
        BE.AppendLine("clsFineUI.Grid.ShowGridHeader = True")
        BE.AppendLine("clsFineUI.Grid.EnableHeaderMenu = True")
        BE.AppendLine("clsFineUI.Grid.EnableColumnHide = True")
        Build_ShowGridColumns()
        BE.AppendLine("clsFineUI.Grid.AddEditCloneDelete()")
        BE.AppendLine("clsFineUI.Grid.AddLikeComment()")
        BE.AppendLine("If Len(strFilter) > 0 Then clsFineUI.Grid.Filter = strFilter")
        BE.AppendLine("clsFineUI.Grid.OrderBy = " & clsDL.QuoteString(PrimaryKey))
        BE.AppendLine("clsFineUI.Grid.BuildSelectCommand()")
        BE.AppendLine("clsFineUI.Grid.UseSQLCompact = True")
        BE.AppendLine("clsFineUI.Grid.Update(Nothing)")
        BE.AppendLine("End Sub")
    End Sub

    Public Shared Sub Build_GridColumns()
        ' column definitions based on columns defined in the columns table
        Dim xTable As DataTable = SQLCompact.OpenDataTable("select * From Columns Where TableID = " & TableID & " and IsOnGrid = 'Y' order by [Position];")
        For Each xRow As DataRow In xTable.Rows
            Dim sColumnName As String = xRow("ColumnName").ToString
            Dim sControlType As String = xRow("ControlType").ToString
            Dim sDataFormat As String = xRow("DataFormat").ToString
            Dim sColumnWidth As String = xRow("ColumnWidth").ToString
            Dim sTextAlign As String = xRow("TextAlign").ToString
            Dim sDataType As String = xRow("DataType").ToString
            Dim sCaption As String = xRow("Caption").ToString
            Dim sIsExpand As String = xRow("IsExpand").ToString
            Dim sIsSortable As String = xRow("IsSortable").ToString
            Dim sMultiLine As String = "False"
            Dim sIsReadOnly As String = xRow("IsReadOnly").ToString

            sIsExpand = IIf((sIsExpand = "Y"), "True", "False")
            sIsSortable = IIf((sIsSortable = "Y"), "True", "False")
            If sDataType = "LongText" Then sMultiLine = "True"
            sIsReadOnly = IIf((sIsReadOnly = "Y"), "False", "True")

            Dim sLine As String = ""
            Select Case sControlType
                Case "Image"
                    PropertyBuilder.Reset()
                    PropertyBuilder.AddProperty(sColumnName)
                    PropertyBuilder.AddProperty(sCaption)
                    PropertyBuilder.AddProperty(sDataFormat)
                    PropertyBuilder.AddProperty(sColumnWidth)
                    PropertyBuilder.AddProperty("FineUi.TextAlign." & sTextAlign, False)
                    PropertyBuilder.AddProperty(sIsExpand, False)
                    PropertyBuilder.AddProperty(sColumnWidth)
                    PropertyBuilder.AddProperty(sColumnWidth)
                    sLine = "clsFineUI.Grid.AddImage(" & PropertyBuilder.Build & ")"
                Case "HyperLink"
                Case "HtmlEditor"
                Case "CheckBox"
                    PropertyBuilder.Reset()
                    PropertyBuilder.AddProperty(sColumnName)
                    PropertyBuilder.AddProperty(sCaption)
                    PropertyBuilder.AddProperty("")
                    PropertyBuilder.AddProperty(sColumnWidth)
                    sLine = "clsFineUI.Grid.AddCheckBoxWeb(" & PropertyBuilder.Build & ")"
                Case "TextBox", "DatePicker", "TextArea"
                    sLine = "clsFineUI.Grid.AddTextBox(" & clsDL.QuoteString(sColumnName) & ", " & _
                                   clsDL.QuoteString(sCaption) & ", " & clsDL.QuoteString(sDataFormat) & ", " & _
                                   clsDL.QuoteString("") & ", " & clsDL.QuoteString(sColumnWidth) & ", FineUi.TextAlign." & sTextAlign & ", False, " & _
                                   sMultiLine & ", " & sIsReadOnly & ")"
                Case "Label"
                    sLine = "clsFineUI.Grid.AddColumn(" & clsDL.QuoteString(sColumnName) & ", " & _
                                   clsDL.QuoteString(sCaption) & ", " & clsDL.QuoteString(sDataFormat) & ", " & _
                                   clsDL.QuoteString("") & ", " & clsDL.QuoteString("") & ", " & sIsExpand & ", " & _
                                   clsDL.QuoteString(sColumnWidth) & ", FineUi.TextAlign." & sTextAlign & ", " & sIsSortable & ")"
                Case "ImageText"
                Case "TextImage"
                Case "RadioButton"
                Case "FileUpload"
                    'clsFineUI.Grid.AddFileUpload("uploader", "Upload", "", "", "150", TextAlign.Left, False)
                    PropertyBuilder.Reset()
                    PropertyBuilder.AddProperty("uploader")
                    PropertyBuilder.AddProperty(sCaption)
                    PropertyBuilder.AddProperty("")
                    PropertyBuilder.AddProperty("")
                    PropertyBuilder.AddProperty(sColumnWidth)
                    PropertyBuilder.AddProperty("FineUi.TextAlign." & sTextAlign, False)
                    PropertyBuilder.AddProperty("False", False)
                    sLine = "clsFineUI.Grid.AddFileUpload(" & PropertyBuilder.Build & ")"
                Case "LinkButton"
                Case "DropDownList"
                    PropertyBuilder.Reset()
                    PropertyBuilder.AddProperty(sColumnName)
                    PropertyBuilder.AddProperty(sCaption)
                    PropertyBuilder.AddProperty(sDataFormat)
                    PropertyBuilder.AddProperty("")
                    PropertyBuilder.AddProperty(sColumnWidth)
                    PropertyBuilder.AddProperty("FineUi.TextAlign." & sTextAlign, False)
                    PropertyBuilder.AddProperty(sIsExpand, False)
                    PropertyBuilder.AddProperty("Nothing", False)
                    sLine = "clsFineUI.Grid.AddComboBox(" & PropertyBuilder.Build & ")"
                Case "HiddenField"
                Case "NumberBox"
                Case "TimePicker"
                Case "TriggerBox"
                Case "TwinTriggerBox"
                Case "Accordion"
                Case "ContentPanel"
                Case "Form"
                Case "GroupPanel"
                Case "Panel"
                Case "SimpleForm"
                Case "Tree"
                Case "Window"
                Case "Grid"
                Case "RegionPanel"
                Case "TabStrip"
            End Select
            If Len(sLine) > 0 Then BE.AppendLine(sLine)
        Next
    End Sub


    Public Shared Sub Build_ShowGridColumns()
        ' column definition of read only grid
        Dim xTable As DataTable = SQLCompact.OpenDataTable("select * From Columns Where TableID = " & TableID & " and IsOnGrid = 'Y' order by [Position];")
        For Each xRow As DataRow In xTable.Rows
            Dim sColumnName As String = xRow("ColumnName").ToString
            Dim sControlType As String = xRow("ControlType").ToString
            Dim sDataFormat As String = xRow("DataFormat").ToString
            Dim sColumnWidth As String = xRow("ColumnWidth").ToString
            Dim sTextAlign As String = xRow("TextAlign").ToString
            Dim sDataType As String = xRow("DataType").ToString
            Dim sCaption As String = xRow("Caption").ToString
            Dim sIsExpand As String = xRow("IsExpand").ToString
            Dim sIsSortable As String = xRow("IsSortable").ToString
            Dim sMultiLine As String = "False"
            Dim sIsReadOnly As String = xRow("IsReadOnly").ToString

            sIsExpand = IIf((sIsExpand = "Y"), "True", "False")
            sIsSortable = IIf((sIsSortable = "Y"), "True", "False")
            If sDataType = "LongText" Then sMultiLine = "True"
            sIsReadOnly = IIf((sIsReadOnly = "Y"), "False", "True")

            Dim sLine As String = ""
            sLine = "clsFineUI.Grid.AddColumn(" & clsDL.QuoteString(sColumnName) & ", " & _
                                   clsDL.QuoteString(sCaption) & ", " & clsDL.QuoteString(sDataFormat) & ", " & _
                                   clsDL.QuoteString("") & ", " & clsDL.QuoteString("") & ", " & sIsExpand & ", " & _
                                   clsDL.QuoteString(sColumnWidth) & ", FineUi.TextAlign." & sTextAlign & ", " & sIsSortable & ")"
            If Len(sLine) > 0 Then BE.AppendLine(sLine)
        Next
    End Sub

    Private Shared Sub Build_Properties()
        ' build properties of the class
        DateFields = New List(Of String)
        FieldsToUpdate = New List(Of String)
        Fields = New List(Of FieldDataType)

        Dim xTable As DataTable = SQLCompact.OpenDataTable("select ColumnName,DataType,DefaultValue from [Columns] where TableID = " & TableID & " order by [Position];")
        For Each xRow As DataRow In xTable.Rows
            Dim sColumnName As String = xRow("ColumnName").ToString
            Dim sDataType As String = xRow("DataType").ToString
            Dim sDefaultValue As String = xRow("DefaultValue").ToString
            Dim sLine As String = "    Public Property " & sColumnName & " As " & VbTypeFromDataType(sDataType) & " = " & VbDefaultFromDataType(sDataType, sDefaultValue)
            BE.AppendLine(sLine)
            ' if is date add it to date fields
            If sDataType.ToLower = "date" Then DateFields.Add(sColumnName)
            ' update fields to update
            FieldsToUpdate.Add(sColumnName)
            ' store column name and datatype
            Dim nFld As New FieldDataType(sColumnName, VbTypeFromDataType(sDataType), VbDefaultFromDataType(sDataType, sDefaultValue), VbDataTypePrefix(sDataType))
            Fields.Add(nFld)
        Next
        ' add extra properties
        BE.AppendLine("    Public Property IsNew As Boolean = False")
        BE.AppendLine("    Public Property DateFields As String = " & clsDL.QuoteString(clsDL.ListToString(DateFields, ",")))
        BE.AppendLine("    Public Property FieldsToUpdate As String = " & clsDL.QuoteString(clsDL.ListToString(FieldsToUpdate, ",")))

    End Sub

    Private Shared Sub Build_ClearNew()

        BE.AppendLine("")
        AddComments("The new method of the class to create new " & TableName & " records.")
        BE.AppendLine("Public Sub New()")
        BE.AppendLine("    MyBase.New()")
        BE.AppendLine("    Clear()")
        BE.AppendLine("    IsNew = True")
        BE.AppendLine("    DateFields = " & clsDL.QuoteString(clsDL.ListToString(DateFields, ",")))
        BE.AppendLine("    FieldsToUpdate = " & clsDL.QuoteString(clsDL.ListToString(FieldsToUpdate, ",")))
        BE.AppendLine("End Sub")

        ' build clear values
        BE.AppendLine("")
        AddComments("The Clear method clears all properties of the business object to defaults.")
        BE.AppendLine("Public Sub Clear()")
        For Each nFld As FieldDataType In Fields
            Dim sLine As String = nFld.FieldName & " = " & nFld.FieldDefault
            BE.AppendLine("    " & sLine)
        Next
        BE.AppendLine("End Sub")

    End Sub

    Private Shared Sub Build_Read()
        BE.AppendLine("")
        AddComments("Read Method to read " & TableName & " record from the database using a primarykey.")
        BE.AppendLine("Public Sub Read(ByVal sID As String)")
        BE.AppendLine("Try")
        BE.AppendLine("Clear()")
        BE.AppendLine("Dim nRead As New DataSet()")
        BE.AppendLine("nRead = SQLCompact.RecordSeek(" & clsDL.QuoteString(TableName) & ", " & clsDL.QuoteString("ID") & _
                      ", sID, " & clsDL.QuoteString("*") & ", " & clsDL.QuoteString("Integer") & ")")
        BE.AppendLine("Dim xCount As Boolean = nRead.Tables(0).Rows.Count - 1 <> -1")
        BE.AppendLine("If xCount Then")
        BE.AppendLine("Dim xRow As DataRow = nRead.Tables(0).Rows(0)")
        For Each nFld As FieldDataType In Fields
            BE.AppendLine(nFld.FieldName & " = " & nFld.ReadValue)
        Next
        BE.AppendLine("Me.IsNew = False")
        BE.AppendLine("nRead.Dispose()")
        BE.AppendLine("End If")
        BE.AppendLine("Catch exc as Exception")
        BE.AppendLine("Throw new Exception(exc.Message)")
        BE.AppendLine("End Try")
        BE.AppendLine("End Sub")
    End Sub

    Private Shared Sub Build_Save()
        BE.AppendLine("")
        AddComments("Save a " & TableName & " record to the database using a primary key. This uses a property get and FieldsToUpdate, DateFields to update the database.")
        BE.AppendLine("Public Sub Save()")
        BE.AppendLine("Try")
        BE.AppendLine("Dim strTable As String = " & clsDL.QuoteString(TableName))
        BE.AppendLine("Dim strPriKey As String = " & clsDL.QuoteString("ID"))
        BE.AppendLine("Dim CommText As String = " & clsDL.QuoteString(""))
        BE.AppendLine("Dim xCommand As New SqlCeCommand")
        BE.AppendLine("If ID = -1 Then")
        BE.AppendLine("ID = SQLCompact.TableMaxValue(strTable, strPriKey, " & clsDL.QuoteString("") & ") + 1")
        BE.AppendLine("IsNew = True")
        BE.AppendLine("Else")
        BE.AppendLine("IsNew = False")
        BE.AppendLine("End If")

        BE.AppendLine("If FieldsToUpdate = " & clsDL.QuoteString("*") & " Then")
        BE.AppendLine("FieldsToUpdate = SQLCompact.Table_FieldNames(strTable, False, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("End If")
        BE.AppendLine("FieldsToUpdate = clsDL.MvRemoveDuplicates(Me.FieldsToUpdate, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("Dim strParameters As String = clsDL.MvPrefixBy(FieldsToUpdate, " & clsDL.QuoteString("@") & ", " & clsDL.QuoteString(",") & ")")

        BE.AppendLine("If IsNew Then")
        BE.AppendLine("CommText = clsDL.BuildInsertCommand(strTable, FieldsToUpdate, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("Else")
        BE.AppendLine("CommText = clsDL.BuildUpdateCommand(strTable, FieldsToUpdate, strPriKey, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("End If")
        BE.AppendLine("xCommand = SQLCompact.OpenCommand(CommText)")
        BE.AppendLine("Dim spFields() As String = Split(FieldsToUpdate, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("With xCommand")
        BE.AppendLine(".Parameters.Clear()")
        BE.AppendLine("For each strField As String In spFields")
        BE.AppendLine("Dim datePos As Integer = clsDL.MvSearch(DateFields, strField, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("If datePos > 0 Then")
        BE.AppendLine("Dim strValue as string = clsDL.PropertyGet(Me, strField).ToString")
        BE.AppendLine(".Parameters.AddWithValue(" & clsDL.QuoteString("@") & " & strField, IIf((IsDate(strValue) = True), CDate(strValue), DBNull.Value))")
        BE.AppendLine("Else")
        BE.AppendLine(".Parameters.AddWithValue(" & clsDL.QuoteString("@") & " & strField, clsDL.PropertyGet(Me, strField))")
        BE.AppendLine("End If")
        BE.AppendLine("Next")
        BE.AppendLine(".ExecuteNonQuery()")
        BE.AppendLine("End With")
        BE.AppendLine("SQLCompact.Connection.Close()")
        BE.AppendLine("xCommand.Dispose()")
        BE.AppendLine("Catch exc as Exception")
        BE.AppendLine("Throw new Exception(exc.Message)")
        BE.AppendLine("End Try")
        BE.AppendLine("End Sub")
    End Sub

    Private Shared Sub Build_Blank()
        Dim cName As String = TableName.Replace(" ", "").Replace("_", "")
        BE.AppendLine("")
        AddComments("Define a blank " & TableName & " record and save it in a database. This method is useful for a grid like interface.")
        BE.AppendLine("Public Sub Blank()")
        BE.AppendLine("Dim clsNew" & cName & " As New cls" & cName)
        BE.AppendLine("clsNew" & cName & ".Clear()")
        BE.AppendLine("clsNew" & cName & ".IsNew = True")
        BE.AppendLine("clsNew" & cName & "." & PrimaryKey & " = -1")
        BE.AppendLine("clsNew" & cName & ".Save")
        BE.AppendLine("clsNew" & cName & " = Nothing")
        BE.AppendLine("End Sub")
    End Sub

    Private Shared Sub Build_Import()
        Dim cName As String = TableName.Replace(" ", "").Replace("_", "")
        BE.AppendLine("")
        AddComments("Import " & TableName & " records from a csv file.")
        BE.AppendLine("Public Shared Sub ImportFile(ByVal SourceFile As String, ByVal FileData As String)")
        BE.AppendLine("Try")
        BE.AppendLine("Dim spLines As String() = Strings.Split(FileData, vbCr & vbLf)")
        BE.AppendLine("For each strLine As String In spLines")
        BE.AppendLine("strLine = strLine.Trim")
        BE.AppendLine("If Len(strLine) > 0 Then")
        BE.AppendLine("Dim spLine As String() = Strings.Split(strLine, " & clsDL.QuoteString(",") & ")")
        BE.AppendLine("Dim clsNew" & cName & " As New cls" & cName)
        BE.AppendLine("With clsNew" & cName)
        BE.AppendLine(".IsNew = True")
        BE.AppendLine("." & PrimaryKey & " = -1")
        BE.AppendLine(".Save")
        BE.AppendLine("End With")
        BE.AppendLine("End If")
        BE.AppendLine("Next")
        BE.AppendLine("Catch exc as exception")
        BE.AppendLine("Throw new Exception(exc.message)")
        BE.AppendLine("End Try")
        BE.AppendLine("End Sub")
    End Sub

    Private Shared Sub Build_Delete()
        ' build delete by

        BE.AppendLine("")
        AddComments("Delete " & TableName & " records specified by a list of ID.")
        BE.AppendLine("    Public Shared Sub Delete(ByRef Records As List(Of String))")
        BE.AppendLine("        For Each rsStr As String In Records")
        BE.AppendLine("            SQLCompact.RunQuery(" & clsDL.Quote & "delete from [" & TableName & "] where ID = " & clsDL.Quote & " & rsStr, True)")
        BE.AppendLine("        Next")
        BE.AppendLine("    End Sub")
        BE.AppendLine("")

        BE.AppendLine("")
        BE.AppendLine("Public Shared Sub DeleteForFile(StrID As String)")
        BE.AppendLine("SQLCompact.RunQuery(" & clsDL.Quote & "DELETE from [" & TableName & "] where SourceFile = '" & clsDL.Quote & " & StrID & " & clsDL.Quote & "';" & clsDL.Quote & ", True)")
        BE.AppendLine("End Sub")

        Dim sLine As String = ""
        For Each nFld As FieldDataType In Fields
            BE.AppendLine("")
            AddComments("Delete " & TableName & " records meeting a particular field name criteria. In this case, " & nFld.FieldName)
            sLine = "Public Shared Sub DeleteBy" & nFld.FieldName & "(ByVal " & nFld.FieldVariable & " As " & nFld.FieldType & ")"
            BE.AppendLine(sLine)
            sLine = "    SQLCompact.RunQuery(" & clsDL.Quote & "Delete from [" & TableName & "] where [" & nFld.FieldName & "] = " & nFld.Filter & ")"
            BE.AppendLine(sLine)
            BE.AppendLine("End Sub")
            BE.AppendLine("")
            AddComments("Delete " & TableName & " records from a list of particular field. In this case, " & nFld.FieldName)
            sLine = "Public Shared Sub DeleteByListOf" & nFld.FieldName & "(ByRef Records As List(Of String))"
            BE.AppendLine(sLine)
            BE.AppendLine("        For Each " & nFld.FieldVariable & " As String In Records")
            sLine = "    SQLCompact.RunQuery(" & clsDL.Quote & "Delete from [" & TableName & "] where [" & nFld.FieldName & "] = " & nFld.Filter & ")"
            BE.AppendLine(sLine)
            BE.AppendLine("        Next")
            BE.AppendLine("End Sub")
        Next
    End Sub

    Private Shared Sub Build_Exists()
        ' build exists
        Dim sLine As String = ""
        ' master
        BE.AppendLine("")
        AddComments("Return True or False if a particular " & TableName & " record exists.")
        BE.AppendLine("Public Shared Function Exists(ByVal intID as Integer) As Boolean")
        BE.AppendLine("Dim flag As Boolean = SQLCompact.RecordExists(" & clsDL.QuoteString(TableName) & ", " & clsDL.QuoteString("ID") & ", intID, " & clsDL.QuoteString("Long") & ")")
        BE.AppendLine("Return flag")
        BE.AppendLine("End Function")
        BE.AppendLine("")

        For Each nFld As FieldDataType In Fields
            BE.AppendLine("")
            AddComments("Return True or False if a particular " & TableName & " record exists based on " & nFld.FieldName)
            sLine = "Public Shared Function ExistsBy" & nFld.FieldName & "(ByVal " & nFld.FieldVariable & " As " & nFld.FieldType & ") As Boolean"
            BE.AppendLine(sLine)
            BE.AppendLine("Dim flag As Boolean = SQLCompact.RecordExists(" & clsDL.QuoteString(TableName) & ", " & clsDL.QuoteString(nFld.FieldName) & ", " & nFld.FieldVariable & ", " & clsDL.QuoteString(nFld.FieldType) & ")")
            BE.AppendLine("Return flag")
            BE.AppendLine("End Function")
        Next
    End Sub

    Private Shared Sub Build_Count()
        ' build count
        Dim sLine As String = ""
        ' master
        BE.AppendLine("")
        AddComments("Return the total number of records in the table " & TableName)
        BE.AppendLine("Public Shared Function Count() As Integer")
        BE.AppendLine("Dim nRecords As Integer = 0")
        BE.AppendLine("Dim dt As DataTable = SQLCompact.OpenDataTable(" & clsDL.Quote & "select Count(ID) as Records from [" & TableName & "]" & clsDL.Quote & ")")
        BE.AppendLine("For Each dr As DataRow In dt.Rows")
        BE.AppendLine("nRecords = Val(dr(" & clsDL.QuoteString("Records") & ").ToString)")
        BE.AppendLine("Next")
        BE.AppendLine("Return nRecords")
        BE.AppendLine("End Function")
        BE.AppendLine("")

        ' individual
        For Each nFld As FieldDataType In Fields
            BE.AppendLine("")
            AddComments("Return the total number of records with a particular value in a field. In this case " & TableName & "." & nFld.FieldName)
            sLine = "Public Shared Function CountBy" & nFld.FieldName & "(ByVal " & nFld.FieldVariable & " As " & nFld.FieldType & ") As Integer"
            BE.AppendLine(sLine)
            BE.AppendLine("Dim sQry As String = " & clsDL.Quote & "Select Count(ID) As Records from [" & TableName & "] where [" & nFld.FieldName & "] = " & nFld.Filter)
            BE.AppendLine("Dim dt As DataTable = SQLCompact.OpenDataTable(sQRY)")
            BE.AppendLine("Dim nRecords as integer = 0")
            BE.AppendLine("For Each dr As DataRow In dt.Rows")
            BE.AppendLine("nRecords = Val(dr(" & clsDL.QuoteString("Records") & ").ToString)")
            BE.AppendLine("Next")
            BE.AppendLine("Return nRecords")
            BE.AppendLine("End Function")
        Next
    End Sub

    Private Shared Sub Build_Lists()
        ' build lists
        Dim sLine As String = ""
        For Each nFld As FieldDataType In Fields
            BE.AppendLine("")
            AddComments("Return the list of strings of a particular field. In this case " & TableName & "." & nFld.FieldName)
            sLine = "    Public Shared Function " & nFld.FieldName & "List(Optional bAddNone As Boolean = False, Optional bIsDate As Boolean = False, Optional FldDelim As String = " & clsDL.QuoteString(";") & ") As List(Of String)"
            BE.AppendLine(sLine)
            sLine = "        Dim strs As List(Of String) = SQLCompact.TableDistinctList(" & clsDL.QuoteString(TableName) & ", " & clsDL.QuoteString(nFld.FieldName) & ", " & clsDL.QuoteString("{0}") & ", bAddNone, bIsDate, FldDelim)"
            BE.AppendLine(sLine)
            BE.AppendLine("        Return strs")
            BE.AppendLine("    End Function")
        Next
    End Sub

    Private Shared Sub Build_ListsBy()
        ' build fieldby
        Dim sLine As String = ""
        For Each nFld As FieldDataType In Fields
            For Each rFld As FieldDataType In Fields
                BE.AppendLine("")
                sLine = "Public Shared Function " & nFld.FieldName & "By" & rFld.FieldName & "(ByVal " & rFld.FieldVariable & " As " & rFld.FieldType & ") As " & nFld.FieldType
                BE.AppendLine(sLine)
                sLine = "    Dim mv As String = SQLCompact.RecordReadToMv(" & clsDL.QuoteString(TableName) & _
                    ", " & clsDL.QuoteString(rFld.FieldName) & ", " & rFld.FieldVariable & ", " & _
                    clsDL.QuoteString(nFld.FieldName) & ", " & clsDL.QuoteString(";") & ", " & clsDL.QuoteString(rFld.FieldType) & ")"
                BE.AppendLine(sLine)
                BE.AppendLine("    Return clsDL.MvField(mv, 1, " & clsDL.QuoteString(";") & ")")
                BE.AppendLine("End Function")
                BE.AppendLine("")
                sLine = "Public Shared Function " & rFld.FieldName & "By" & nFld.FieldName & "(ByVal " & nFld.FieldVariable & " As " & nFld.FieldType & ") As " & rFld.FieldType
                BE.AppendLine(sLine)
                sLine = "    Dim mv As String = SQLCompact.RecordReadToMv(" & clsDL.QuoteString(TableName) & _
                    ", " & clsDL.QuoteString(nFld.FieldName) & ", " & nFld.FieldVariable & ", " & _
                    clsDL.QuoteString(rFld.FieldName) & ", " & clsDL.QuoteString(";") & ", " & clsDL.QuoteString(nFld.FieldType) & ")"
                BE.AppendLine(sLine)
                BE.AppendLine("    Return clsDL.MvField(mv, 1, " & clsDL.QuoteString(";") & ")")
                BE.AppendLine("End Function")
                BE.AppendLine("")
            Next
        Next
    End Sub

    Private Shared Sub Build_FieldBy()
        ' build fieldby
        Dim masterControl As New List(Of String)
        Dim sLine As String = ""
        Dim sControl As String = ""
        For Each nFld As FieldDataType In Fields
            For Each rFld As FieldDataType In Fields
                sControl = nFld.FieldName & "By" & rFld.FieldName
                If masterControl.Contains(sControl) = False Then
                    masterControl.Add(sControl)
                    BE.AppendLine("")
                    AddComments("Return a particular field based on another field. In this case return " & TableName & "." & nFld.FieldName & " based on " & TableName & "." & rFld.FieldName)
                    sLine = "Public Shared Function " & nFld.FieldName & "By" & rFld.FieldName & "(ByVal " & rFld.FieldVariable & " As " & rFld.FieldType & ") As " & nFld.FieldType
                    BE.AppendLine(sLine)
                    sLine = "    Dim mv As String = SQLCompact.RecordReadToMv(" & clsDL.QuoteString(TableName) & _
                        ", " & clsDL.QuoteString(rFld.FieldName) & ", " & rFld.FieldVariable & ", " & _
                        clsDL.QuoteString(nFld.FieldName) & ", " & clsDL.QuoteString(";") & ", " & clsDL.QuoteString(rFld.FieldType) & ")"
                    BE.AppendLine(sLine)
                    BE.AppendLine("    mv = clsDL.MvField(mv, 1, " & clsDL.QuoteString(";") & ")")
                    BE.AppendLine("    Return " & nFld.ByReturnValue)
                    BE.AppendLine("End Function")
                    BE.AppendLine("")
                End If
                sControl = rFld.FieldName & "By" & nFld.FieldName
                If masterControl.Contains(sControl) = False Then
                    masterControl.Add(sControl)
                    BE.AppendLine("")
                    AddComments("Return a particular field based on another field. In this case return " & TableName & "." & rFld.FieldName & " based on " & TableName & "." & nFld.FieldName)
                    sLine = "Public Shared Function " & rFld.FieldName & "By" & nFld.FieldName & "(ByVal " & nFld.FieldVariable & " As " & nFld.FieldType & ") As " & rFld.FieldType
                    BE.AppendLine(sLine)
                    sLine = "    Dim mv As String = SQLCompact.RecordReadToMv(" & clsDL.QuoteString(TableName) & _
                        ", " & clsDL.QuoteString(nFld.FieldName) & ", " & nFld.FieldVariable & ", " & _
                        clsDL.QuoteString(rFld.FieldName) & ", " & clsDL.QuoteString(";") & ", " & clsDL.QuoteString(nFld.FieldType) & ")"
                    BE.AppendLine(sLine)
                    BE.AppendLine("    mv = clsDL.MvField(mv, 1, " & clsDL.QuoteString(";") & ")")
                    BE.AppendLine("    Return " & rFld.ByReturnValue)
                    BE.AppendLine("End Function")
                    BE.AppendLine("")
                End If
            Next
        Next
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class
